package com.client.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.client.bean.BirdList;
import com.client.bean.BirdMessage;
import com.client.bean.User;
import com.clinet.cache.Log;

public class DataService {
	final static String TAG = "test";
	private DatabaseHelper2 DatabaseHelper2;

	public DataService(Context context) {
		DatabaseHelper2 = new DatabaseHelper2(context);
	}

	public void closeDB() {
		DatabaseHelper2.close();
	}

	/**
	 * 收藏一个账号
	 * 
	 * @param mUser
	 */
	public void saveUser(User mUser) {
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		StringBuffer insertSQL = new StringBuffer();
		insertSQL.append("insert into USER(");
		insertSQL.append("deviceName  ,");
		insertSQL.append("deviceType  ,");
		insertSQL.append("deviceNum  ,");
		insertSQL.append("userName  ,");
		insertSQL.append("password  , ");
		insertSQL.append("unitName  , ");
		insertSQL.append("autologinStr  , ");
		insertSQL.append("rememberStr  , ");
		insertSQL.append("current  , ");
		insertSQL.append("lasttime  , ");
		insertSQL.append("userId  )");
		insertSQL.append("  values(" + "?,?,?,?,?,?,?,?,?,?,?)");

		db.execSQL(insertSQL.toString(),
				new Object[] { mUser.deviceName, mUser.deviceType,
						mUser.deviceNum, mUser.userName, mUser.password,
						mUser.unitName, mUser.autologinStr, mUser.rememberStr,
						mUser.current, mUser.lasttime, mUser.userId });
		db.close();

	}

	/**
	 * 删除一个账号
	 * 
	 * @param id
	 */
	public void deleteUser(String username) {
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		db.execSQL("delete from USER where username=?",
				new Object[] { username });
		db.close();
	}

	public void updateUser(User mUser) {

		if (hasUser(mUser)) {
			SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
			db.execSQL("update USER set deviceName=? ",
					new Object[] { "false" });
			mUser.current = "true";
			db.execSQL(
					"update USER  set deviceName=?, deviceType=?     ,deviceNum =? ,userName=?  ,password=?  ,unitName=?  , autologinStr=?  , rememberStr=?  , current=?  , lasttime=?  ,userId=? ",
					new Object[] { mUser.deviceName, mUser.deviceType,
							mUser.deviceNum, mUser.userName, mUser.password,
							mUser.unitName, mUser.autologinStr,
							mUser.rememberStr, mUser.current, mUser.lasttime,
							mUser.userId });
			db.close();
		} else {
			saveUser(mUser);
		}

	}

	/**
	 * 是否已存USER
	 * 
	 * @param username
	 * @return
	 */
	public boolean hasUser(User user) {
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select count(*) from USER where username = ? and password  = ?",
						new String[] { user.userName, user.password });
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		cursor.close();
		db.close();
		if (count > 0) {
			return true;
		}
		return false;
	}

	public boolean hasInitUser() {
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select count(*) from USER where current = ? ",
				new String[] { "true" });
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		cursor.close();
		db.close();
		if (count > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 
	 * 获取用户列表
	 * 
	 * @return
	 */
	public ArrayList<User> getUserList() {
		ArrayList<User> mUsers = new ArrayList<User>();
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from USER", new String[] {});
		while (cursor.moveToNext()) {
			mUsers.add(mapRow2User(cursor));
		}
		cursor.close();
		db.close();
		return mUsers;
	}

	/**
	 * 获得当前用户
	 * 
	 * @return
	 */
	public User getCurrUser() {
		User mUser = null;
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from USER where current like ?",
				new String[] { "%true%" });
		while (cursor.moveToNext()) {
			mUser = mapRow2User(cursor);
		}
		cursor.close();
		db.close();
		// if (mUser != null)// 判断是否有oauth信息
		// {
		// if ("".equals(mUser.userId)) {
		// mUser = null;
		// }
		// }

		return mUser;
	}

	/**
	 * 是否已存ModelOrPrice
	 * 
	 * @param subid
	 * @return
	 */
	public boolean hasModelOrPrice(String subid) {
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select count(*) from MODELORPRICE  where subid like ?",
				new String[] { "%" + subid + "%" });
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		cursor.close();
		db.close();
		if (count > 0) {
			return true;
		}
		return false;
	}

	private User mapRow2User(Cursor cursor) {
		User mUser = new User();
		mUser.deviceName = cursor.getString(0);
		mUser.deviceType = cursor.getString(1); // 41
		mUser.deviceNum = cursor.getString(2);
		mUser.userName = cursor.getString(3);
		mUser.password = cursor.getString(4);
		mUser.unitName = cursor.getString(5);
		mUser.autologinStr = cursor.getString(6);
		mUser.rememberStr = cursor.getString(7);
		mUser.lasttime = cursor.getString(8);
		mUser.userId = cursor.getString(9);
		return mUser;
	}

	// ----------------------
	public void saveBirdMessage(BirdList mBirdList) {
		List<BirdMessage> mBirdMessages = mBirdList.getItems();
		if (mBirdMessages.size() <= 0) {
			return;
		}
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		for (BirdMessage mBirdMessage : mBirdMessages) {
			Log.i(mBirdMessage.toString());
			StringBuffer insertSQL = new StringBuffer();
			insertSQL.append("insert into BIRDMESSAGE(");
			insertSQL.append("birdId  ,");
			insertSQL.append("birdImage  ,");
			insertSQL.append("date  ,");
			insertSQL.append("birdName  ,");
			insertSQL.append("num  , ");

			insertSQL.append("kind  , ");
			insertSQL.append("healthNum  , ");
			insertSQL.append("sickNum  , ");
			insertSQL.append("sickImage  , ");
			insertSQL.append("sickDes  , ");

			insertSQL.append("deadNum  , ");
			insertSQL.append("deadImage  , ");
			insertSQL.append("deadDes  , ");
			insertSQL.append("collectLoaction  , ");
			insertSQL.append("place  , ");
			insertSQL.append("direct  , ");

			insertSQL.append("distance  , ");
			insertSQL.append("stayPlace  , ");
			insertSQL.append("longitude  , ");
			insertSQL.append("latitude  , ");
			insertSQL.append("uploaded  , ");
			insertSQL.append("pinyin  , ");
			insertSQL.append("clickNum  , ");
			insertSQL.append("lasttime  , ");
			insertSQL.append("localId  , ");
			insertSQL.append("status  )");
			insertSQL.append("  values("
					+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

			Log.i(mBirdMessage.toString());
			db.execSQL(insertSQL.toString(), new Object[] {
					mBirdMessage.birdId, mBirdMessage.birdImage,
					mBirdMessage.date, mBirdMessage.birdName, mBirdMessage.num,
					mBirdMessage.kind, mBirdMessage.healthNum,
					mBirdMessage.sickNum, mBirdMessage.sickImage,
					mBirdMessage.sickDes, mBirdMessage.deadNum,
					mBirdMessage.deadImage, mBirdMessage.deadDes,
					mBirdMessage.collectLoaction, mBirdMessage.place,
					mBirdMessage.direct, mBirdMessage.distance,
					mBirdMessage.stayPlace, mBirdMessage.longitude,
					mBirdMessage.latitude, mBirdMessage.uploaded,
					mBirdMessage.pinyin, mBirdMessage.clickNum + "",
					mBirdMessage.lasttime, mBirdMessage.localId,
					mBirdMessage.status });

		}
		db.close();
	}

	/**
	 * 收藏信息
	 * 
	 * @param mUser
	 */
	public void saveBirdMessage(BirdMessage mBirdMessage) {
		Log.i(mBirdMessage.toString());
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		StringBuffer insertSQL = new StringBuffer();
		insertSQL.append("insert into BIRDMESSAGE(");
		insertSQL.append("birdId  ,");
		insertSQL.append("birdImage  ,");
		insertSQL.append("date  ,");
		insertSQL.append("birdName  ,");
		insertSQL.append("num  , ");

		insertSQL.append("kind  , ");
		insertSQL.append("healthNum  , ");
		insertSQL.append("sickNum  , ");
		insertSQL.append("sickImage  , ");
		insertSQL.append("sickDes  , ");

		insertSQL.append("deadNum  , ");
		insertSQL.append("deadImage  , ");
		insertSQL.append("deadDes  , ");
		insertSQL.append("collectLoaction  , ");
		insertSQL.append("place  , ");
		insertSQL.append("direct  , ");

		insertSQL.append("distance  , ");
		insertSQL.append("stayPlace  , ");
		insertSQL.append("longitude  , ");
		insertSQL.append("latitude  , ");
		insertSQL.append("uploaded  , ");
		insertSQL.append("pinyin  , ");
		insertSQL.append("clickNum  , ");
		insertSQL.append("lasttime  , ");
		insertSQL.append("localId  , ");
		insertSQL.append("status  )");
		insertSQL.append("  values("
				+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

		db.execSQL(insertSQL.toString(), new Object[] { mBirdMessage.birdId,
				mBirdMessage.birdImage, mBirdMessage.date,
				mBirdMessage.birdName, mBirdMessage.num, mBirdMessage.kind,
				mBirdMessage.healthNum, mBirdMessage.sickNum,
				mBirdMessage.sickImage, mBirdMessage.sickDes,
				mBirdMessage.deadNum, mBirdMessage.deadImage,
				mBirdMessage.deadDes, mBirdMessage.collectLoaction,
				mBirdMessage.place, mBirdMessage.direct, mBirdMessage.distance,
				mBirdMessage.stayPlace, mBirdMessage.longitude,
				mBirdMessage.latitude, mBirdMessage.uploaded,
				mBirdMessage.pinyin, mBirdMessage.clickNum + "",
				mBirdMessage.lasttime, mBirdMessage.localId,
				mBirdMessage.status });

		db.close();

	}

	/**
	 * 删除一个BirdMessage
	 * 
	 * @param id
	 */
	public void deleteBirdMessage(String id) {
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		db.execSQL("delete from BIRDMESSAGE where birdId=?",
				new Object[] { id });
		db.close();
	}

	// public void updateBirdMessage(BirdMessage mBirdMessage ) {
	// SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
	// db.execSQL("update BIRDMESSAGE set current=? ", new Object[] { "false"
	// });
	// db.execSQL(
	// "update USER set current=?  where username like ? and password like ?",
	// new Object[] { "true", "%" + mUser.userName + "%",
	// "%" + mUser.password + "%" });
	// db.close();
	// }

	public boolean hasBirdList() {
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from BIRDMESSAGE ",
				new String[] {});
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		cursor.close();
		db.close();
		if (count <= 0) {
			Log.i("NEED  REQUEST");
			return false;
		}
		return true;
	}

	public boolean hasBIRDMESSAGE(BirdMessage nBirdMessage) {
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select count(*) from BIRDMESSAGE where localId = ? ",
				new String[] { nBirdMessage.localId });
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		cursor.close();
		db.close();
		if (count > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 
	 * 获取BirdList
	 * 
	 * @return
	 */
	public void getBirdList(BirdList mBirdList, String key) {
		mBirdList.getItems().clear();
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select * from BIRDMESSAGE where birdName like ? or pinyin like ? order by clickNum DESC ",
						new String[] { "%" + key + "%",
								"%" + key.toLowerCase() + "%" });
		while (cursor.moveToNext()) {
			mBirdList.addBirdMessage(mapRow2BirdMessage(cursor));
		}
		cursor.close();
		db.close();
	}

	public void getUnuploadBirdList(BirdList mBirdList) {
		mBirdList.getItems().clear();
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select * from BIRDMESSAGE where uploaded = ?  order by clickNum DESC ",
						new String[] { "false", });
		while (cursor.moveToNext()) {
			mBirdList.addBirdMessage(mapRow2BirdMessage(cursor));
		}
		cursor.close();
		db.close();
	}

	public void getBirdListNoOrder(BirdList mBirdList, String key) {
		mBirdList.getItems().clear();
		SQLiteDatabase db = DatabaseHelper2.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select * from BIRDMESSAGE where birdName like ? or pinyin like ?order  by pinyin ASC ",
						new String[] { "%" + key + "%",
								"%" + key.toLowerCase() + "%" });
		while (cursor.moveToNext()) {
			mBirdList.addBirdMessage(mapRow2BirdMessage(cursor));
		}
		cursor.close();
		db.close();
	}

	private BirdMessage mapRow2BirdMessage(Cursor cursor) {
		BirdMessage mBirdMessage = new BirdMessage();
		mBirdMessage.birdId = cursor.getString(0);
		mBirdMessage.birdImage = cursor.getString(1); // 41
		mBirdMessage.date = cursor.getString(2);
		mBirdMessage.birdName = cursor.getString(3);
		mBirdMessage.num = cursor.getString(4);

		mBirdMessage.kind = cursor.getString(5);
		mBirdMessage.healthNum = cursor.getString(6);
		mBirdMessage.sickNum = cursor.getString(7);
		mBirdMessage.sickImage = cursor.getString(8);
		mBirdMessage.sickDes = cursor.getString(9);

		mBirdMessage.deadNum = cursor.getString(10);
		mBirdMessage.deadImage = cursor.getString(11);
		mBirdMessage.deadDes = cursor.getString(12);
		mBirdMessage.collectLoaction = cursor.getString(13);
		mBirdMessage.place = cursor.getString(14);
		mBirdMessage.direct = cursor.getString(15);

		mBirdMessage.distance = cursor.getString(16);
		mBirdMessage.stayPlace = cursor.getString(17);
		mBirdMessage.longitude = cursor.getString(18);
		mBirdMessage.latitude = cursor.getString(19);
		mBirdMessage.uploaded = cursor.getString(20);
		mBirdMessage.pinyin = cursor.getString(21);
		try {
			mBirdMessage.clickNum = Integer.parseInt(cursor.getString(22));
		} catch (Exception e) {
			mBirdMessage.clickNum = 0;
		}

		mBirdMessage.lasttime = cursor.getString(23);
		mBirdMessage.localId = cursor.getString(24);

		mBirdMessage.status = cursor.getString(25);

		Log.i("mapRow2BirdMessage" + mBirdMessage.toString());
		return mBirdMessage;
	}

	public void updateBirdMessage(BirdMessage mBirdMessage) {
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		Log.i("----------------updateBirdMessageClick-"
				+ mBirdMessage.toString());
		db.execSQL(
				"update BIRDMESSAGE  set  birdImage=?     ,date =? ,birdName=?  ,num=?  ,"
						+ "kind=?  , healthNum=?  , sickNum=?  , sickImage=?  , sickDes=? , "
						+ "deadNum=?  , deadImage=?  , deadDes=?  , collectLoaction=?  , place=? , direct=?  ,"
						+ "distance=?  , stayPlace=?  , longitude=?  , latitude=?  , uploaded=? , pinyin=?, clickNum=?, lasttime=?, status=? , birdId=?where   birdId=? or localId=?",
				new Object[] { mBirdMessage.birdImage, mBirdMessage.date,
						mBirdMessage.birdName, mBirdMessage.num,
						mBirdMessage.kind, mBirdMessage.healthNum,
						mBirdMessage.sickNum, mBirdMessage.sickImage,
						mBirdMessage.sickDes, mBirdMessage.deadNum,
						mBirdMessage.deadImage, mBirdMessage.deadDes,
						mBirdMessage.collectLoaction, mBirdMessage.place,
						mBirdMessage.direct, mBirdMessage.distance,
						mBirdMessage.stayPlace, mBirdMessage.longitude,
						mBirdMessage.latitude, mBirdMessage.uploaded,
						mBirdMessage.pinyin, mBirdMessage.clickNum,
						mBirdMessage.lasttime, mBirdMessage.status,
						mBirdMessage.birdId, mBirdMessage.birdId,
						mBirdMessage.localId });
		db.close();
	}

	public void updateBirdMessageClick(BirdMessage mBirdMessage) {
		SQLiteDatabase db = DatabaseHelper2.getWritableDatabase();
		mBirdMessage.clickNum += 1;
		db.execSQL(
				"update BIRDMESSAGE  set  clickNum=? where birdId=? or localId=? ",
				new Object[] { mBirdMessage.clickNum, mBirdMessage.birdId,
						mBirdMessage.localId });
		Log.i(mBirdMessage.toString());
		db.close();
	}
}
